【アップデート情報】 BigQueryで主キーと外部キーが正式にサポートされるようになりました

#Google Cloud (GCP)

#Google BigQuery

hanzawa.yuya

2023.07.28

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Google Cloudのデータエンジニアをしています、はんざわです。
先日のアップデートで主キーと外部キー正式にGAになりました。

BigQuery release notes

とはいえ、大多数の人が想像するRDBの主キーや外部キーと挙動が違ったりする部分もあるので実際の検証も含めながら触っていきたいと思います。

できないこととできること

できないこと

主キーと外部キーの制約は強制しない、つまりデフォルトでは重複もNULLも許容される。（ユーザー側で制約に一致することを確認する必要がある）。

できること

結合のパフォーマンス大幅改善

さっそくテーブルを作ってみましょう。

主キーと外部キーの登録方法

例として、以下のようなテーブルで考えてみます。

color_table

id	color
1	red
2	yellow
3	green

fruit_table

number	color_id	name
001	1	apple
002	2	remon
003	3	melon

既存のテーブルに主キーや外部キーを登録する場合はALTER TABLE ADD [PRIMARY / FOREIGN] KEY statementを使います。

ALTER TABLE ADD PRIMARY KEY statement

ALTER TABLE ADD FOREIGN KEY statement

実際にcolor_tableのidに主キーを登録し、fruit_tableのnumberに主キーをcolor_idには外部キーを登録します。

ALTER TABLE test.color_table
ADD PRIMARY KEY(id) NOT ENFORCED;

ALTER TABLE test.fruit_table
ADD PRIMARY KEY(number) NOT ENFORCED,
ADD FOREIGN KEY(color_id) REFERENCES test.color_table(id) NOT ENFORCED;

もちろんCREATE TABLE statementで最初から登録することも可能です。

CREATE TABLE statement

重複データを追加してみる

以下のクエリでcolor_tableに重複データを追加してみます。

INSERT test.color_table
SELECT
  1 AS id,
  'red' AS color

color_table

id	color
1	red
1	red
2	yellow
3	green

追加できてしまいました。
冒頭で説明した通り、主キーと外部キーの制約は強制されていませんのでユーザー側で追加できないように条件を定義する必要があります。

結合のパフォーマンス検証

Google Cloud公式のブログにも同様の検証が行われていたので併せて確認してください。

使用するテーブル

今回の検証ではBigQuery public datasetsのwikipediaを使用します。
wikipediaテーブルではrevision_idがユニークな値となっているのでこのカラムを結合キーに使用します。テーブルのサイズと詳細は以下の通りです。

テーブルのサイズ

項目	容量
行数	313,797,035
合計論理バイト数	35.69GB
合計物理バイト数	12.66GB

詳細

These are unique across all revisions to all pages in a particular language and increase with time. Sorting the revisions to a page by revision_id will yield them in chronological order.

これらは、特定の言語のすべてのページのすべてのリビジョンで一意であり、時間とともに増加します。ページのリビジョンをrevision_idで並べ替えると、時系列で表示されます。（DeepL翻訳）

進め方

3種類のケースで結合した際のパフォーマンスを検証したいと思います。

case	対象テーブル1	対象テーブル2	詳細
case1	通常テーブル	通常テーブル	通常のテーブル同士
case2	通常テーブル + 主キー	通常テーブル + 主キーと外部キー	キーを登録したテーブル同士
case3	クラスタ化テーブル + 主キー	クラスタ化テーブル + 主キーと外部キー	キーを登録し、クラスタ化したテーブル同士

使用するクエリ

以下のようなクエリでパフォーマンスの検証をします。クエリ実行時にはキャッシュの設定をオフにしています。
結合の前後で時間を測定し、その結果を保存します。この測定を各ケース毎に10回ずつ行いました。

BEGIN

  /* 使用する変数を定義 */
  DECLARE x INT64 DEFAULT 1;
  DECLARE START_TIME, END_TIME TIMESTAMP;

  /* 結果測定用のテーブル作成 */
  CREATE OR REPLACE TABLE `test.result1` (
    num INT64,
    diff FLOAT64
  );

  REPEAT

    /* 測定開始 */
    SET START_TIME = (SELECT CURRENT_TIMESTAMP());

    /* 対象のテーブルを結合する */
    CREATE OR REPLACE TEMP TABLE test1 AS (
      SELECT
        t1.*
      FROM
        test.wikipedia1 AS t1
      LEFT JOIN
        test.wikipedia2 AS t2
      ON
        t1.revision_id = t2.revision_id
    );

    /* 測定終了 */
    SET END_TIME = (SELECT CURRENT_TIMESTAMP());

    /* 検証結果を保存 */
    INSERT `test.result1`
    SELECT
      x AS num,
      TIMESTAMP_DIFF(END_TIME, START_TIME, millisecond) / 1000 as diff;

    SET x = x + 1;
    SELECT x;
    UNTIL x >= 11
  END REPEAT;

END;

結果

結果は以下の表の通りでした。

num	case1	case2	case3
1	15.837	12.941	12.324
2	14.932	12.773	12.295
3	20.132	15.377	12.749
4	15.983	15.875	13.824
5	14.818	12.763	12.896
6	14.829	15.303	14.853
7	15.43	16.499	11.349
8	15.912	17.617	14.363
9	14.276	11.926	15.307
10	15.319	11.923	14.79